USE [ppjdb]
GO

alter table dbo.tblSessionList add [PopUpFlag] [bit] NOT NULL,
	[DatePopUp] [datetime] NULL,
	[RequestType] [int] NULL
	
GO

alter table dbo.tblShoppingList add [RequestHeaderID] [bigint] NOT NULL
GO

alter table dbo.tblShoppingList drop column [CouponID]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblShoppingList_WSLogID]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblShoppingList]'))
ALTER TABLE [dbo].[tblShoppingList] DROP CONSTRAINT [FK_tblShoppingList_WSLogID]
GO

ALTER TABLE [dbo].[tblShoppingList] DROP COLUMN [WSLogID]
GO

--- DROP tblWSLog table

-- DELETE FK
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblWSLog_SessionID]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblWSLog]'))
ALTER TABLE [dbo].[tblWSLog] DROP CONSTRAINT [FK_tblWSLog_SessionID]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__tblWSLog__OrderT__6754599E]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[tblWSLog] DROP CONSTRAINT [DF__tblWSLog__OrderT__6754599E]
END
GO

-- DELETE ALL DEFAULT CONSTRAINTS
DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

set @database = DB_NAME()
set @table = 'tblWSLog'

DECLARE @sql nvarchar(255)
WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table)
BEGIN
    select    @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME 
    from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    where    constraint_catalog = @database and 
            table_name = @table
    exec    sp_executesql @sql
END

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblWSLog]') AND type in (N'U'))
DROP TABLE [dbo].[tblWSLog]
GO

-- RE-CREATE tblWSLog table
--------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblWSLog](
	[WSLogID] [bigint] IDENTITY(1,1) NOT NULL,
	[Request] [xml] not null,
	[DateRequest] [datetime] not null default (getdate()),
	[Response] [xml] null,
	[DateResponse] [datetime] null
 CONSTRAINT [PK_tblWSLog] PRIMARY KEY CLUSTERED 
(
	[WSLogID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
--------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblRequestHeader](
	[RequestHeaderID] [bigint] IDENTITY(1,1) NOT NULL,
	[WSLogID] [bigint] NOT NULL,
	[RequestType] [int] NULL,
	[StoreID] [bigint] NULL,
	[StoreName] [nvarchar] (255) NULL,
	[StoreGUID] [nvarchar] (255) NULL,
	[StoreRevokeStatus] [bit] NULL,
	[StoreIPListID] [bigint] NULL,
	[StoreIPAddress] [varchar] (64) NULL,
	[StoreDomainName] [nvarchar] (128) NULL,
	[StoreDomainRevokeStatus] [bit] NULL,
	[SessionID] [bigint] NOT NULL,
	[PopUpJoeSessionID] [nvarchar] (256) NULL,
	[CustomerIPAddress] [varchar](64) NULL,
	[CustomerLoginName] [nvarchar](128) NULL,
	[FirstTimeVisitor] [bit] NOT NULL,
	[FirstTimeBuyer] [bit] NOT NULL,
	[LoggedIn] [bit] NOT NULL,
	[OrderTotal] [decimal](7, 2) NOT NULL,
	[ActivateTimer] [char](1) NOT NULL,
	[TimerValue1] [int] NOT NULL,
	[TimerValue2] [int] NOT NULL,
	[eCommCouponID] [nvarchar](64) NULL,
	[CouponID] [bigint] NULL,
	[PopUpFlag] [bit] NOT NULL,
	[DatePopUp] [datetime] NULL,
	[OrderSubmitFlag] [bit] NOT NULL,
	[DateOrderSubmit] [datetime] NULL,
	[DateCreated] [datetime] NOT NULL,
	[UserCreated] [bigint] NOT NULL,
	[DateUpdated] [datetime] NULL,
	[UserUpdated] [bigint] NULL,
 CONSTRAINT [PK_tblRequestHeader] PRIMARY KEY CLUSTERED 
(
	[RequestHeaderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblRequestHeader]  WITH CHECK ADD  CONSTRAINT [FK_tblRequestHeader_SessionID] FOREIGN KEY([SessionID])
REFERENCES [dbo].[tblSessionList] ([SessionID])
GO

ALTER TABLE [dbo].[tblRequestHeader] CHECK CONSTRAINT [FK_tblRequestHeader_SessionID]
GO

ALTER TABLE [dbo].[tblRequestHeader]  WITH CHECK ADD  CONSTRAINT [FK_tblRequestHeader_WSLogID] FOREIGN KEY([WSLogID])
REFERENCES [dbo].[tblWSLog] ([WSLogID])
GO

ALTER TABLE [dbo].[tblRequestHeader] CHECK CONSTRAINT [FK_tblRequestHeader_WSLogID]
GO

ALTER TABLE [dbo].[tblRequestHeader] ADD  DEFAULT ((0)) FOR [OrderTotal]
GO

ALTER TABLE [dbo].[tblRequestHeader] ADD  DEFAULT ('N') FOR [ActivateTimer]
GO

ALTER TABLE [dbo].[tblRequestHeader] ADD  DEFAULT ((0)) FOR [TimerValue1]
GO

ALTER TABLE [dbo].[tblRequestHeader] ADD  DEFAULT ((0)) FOR [TimerValue2]
GO

ALTER TABLE [dbo].[tblRequestHeader] ADD  DEFAULT ((0)) FOR [PopUpFlag]
GO

ALTER TABLE [dbo].[tblRequestHeader] ADD  DEFAULT ((0)) FOR [OrderSubmitFlag]
GO

ALTER TABLE [dbo].[tblRequestHeader] ADD  DEFAULT (getdate()) FOR [DateCreated]
GO

ALTER TABLE [dbo].[tblRequestHeader] ADD  DEFAULT ((0)) FOR [UserCreated]
GO

ALTER TABLE [dbo].[tblRequestHeader] ADD  DEFAULT ((0)) FOR [FirstTimeVisitor]
GO

ALTER TABLE [dbo].[tblRequestHeader] ADD  DEFAULT ((0)) FOR [FirstTimeBuyer]
GO

ALTER TABLE [dbo].[tblRequestHeader] ADD  DEFAULT ((0)) FOR [LoggedIn]
GO
-- \\\\\\\\\\\\\\\\\\\\\\\\


ALTER TABLE [dbo].[tblShoppingList]  WITH CHECK ADD  CONSTRAINT [FK_tblShoppingList_RequestHeaderID] FOREIGN KEY([RequestHeaderID])
REFERENCES [dbo].[tblRequestHeader] ([RequestHeaderID])
GO

ALTER TABLE [dbo].[tblShoppingList] CHECK CONSTRAINT [FK_tblShoppingList_RequestHeaderID]
GO

